﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Configuration;
using IHISCETeim.BLL;

namespace IHISCETeim.SAPSyncSvr
{
    public class SyncSAPDAL
    {
        /// <summary>
        /// SBO连接字符串
        /// </summary>
        protected static string SBOConnStr
        {
            get
            {
                return "SBOConnectionString";
            }
        }
        private static Dictionary<string, string> _ConnStrInfo;
        /// <summary>
        /// 获取SQL联系字符串中的各个项,
        /// 项的标志全部为小写,并去掉项的空格符
        /// 如Data Source已变成datasource
        /// </summary>
        private static Dictionary<string, string> ConnStrInfo
        {
            get
            {
                ///Database DB = DatabaseFactory.CreateDatabase(SBOConnStr);
                if (_ConnStrInfo == null)
                {
                    string SBOconnstr = ConfigurationManager.ConnectionStrings[SBOConnStr].ConnectionString;
                    string[] connectionstring = SBOconnstr.Split(new char[] { ';' });
                    _ConnStrInfo = new Dictionary<string, string>();
                    foreach (string item in connectionstring)
                    {
                        string[] itemarr = item.Split(new char[] { '=' });
                        _ConnStrInfo.Add(itemarr[0].ToLower().Replace(" ", ""), itemarr[1]);
                    }
                }
                return _ConnStrInfo;
            }
        }

        protected static string DataSource
        {
            get
            {
                return ConnStrInfo["datasource"];
            }
        }
        protected static string InitialCatalog
        {
            get
            {
                return ConnStrInfo["initialcatalog"];
            }
        }
        protected static string UserID
        {
            get
            {
                return ConnStrInfo["userid"];
            }
        }
        protected static string Password
        {
            get
            {
                return ConnStrInfo["password"];
            }
        }
        #region 同步相关
        /// <summary>
        /// 同步SAP 索赔件发货记录
        /// </summary>
        public static string SyncUClaimDemage()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncUClaimDemage", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch(Exception e)
            {
                return e.Message;
            }
        }
        /// <summary>
        /// 同步代理商列表
        /// </summary>
        /// <returns></returns>
        public static string SyncAgentList()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncAgentList", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch(Exception e)
            {
                return e.Message;
            }
        }
        /// <summary>
        /// 更新索赔件预计返回日期
        /// 规则:10号之前发货的在30提醒,10到30号发货的在下个月30号提醒
        /// </summary>
        public static string UpdateInWillDate()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemage_UpdateInWillDate");
            try
            {
                DB.ExecuteScalar(dbCommand);
                return "";
            }
            catch (Exception exc)
            {
                return exc.Message;

            }
        }
        /// <summary>
        /// 定时执行索赔费用管控表金额统计程序,用于处理索赔发费滞后于品情单录入的情况
        /// </summary>
        /// <returns></returns>
        public static string FixedTimeExecStatistical()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("UClaimDemageControl_FixedTimeExecStatistical");
            try
            {
                dbCommand.CommandTimeout = 99999;
                DB.ExecuteScalar(dbCommand);
                return "";
            }
            catch (Exception exc)
            {
                return exc.Message;

            }
        }
        #endregion

        #region 生产部物管系统，相关同步
        /// <summary>
        /// 同步SAP 原材料主数据
        /// </summary>
        public static string SyncOITM()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOITM", DataSource, UserID, Password, InitialCatalog);
            dbCommand.CommandTimeout = 6000;
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// 同步SAP 收货采购单
        /// </summary>
        public static string SyncOPDN()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOPDN", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// 同步SAP 库存转储
        /// </summary>
        public static string SyncOWTR()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOWTR", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// 同步SAP 收货
        /// </summary>
        public static string SyncOIGN()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOIGN", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
        /// <summary>
        /// 同步SAP 发货
        /// </summary>
        public static string SyncOIGE()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOIGE", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// 同步SAP 退货
        /// </summary>
        public static string SyncORPD()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncORPD", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }
        /// <summary>
        /// 同步SAP采购订单，用于模具费分摊使用
        /// </summary>
        public static string SyncOPOR()
        {
            string returnstr = "";

            Database DB = DatabaseFactory.CreateDatabase();
            //2014--5-15 改成 所有的物料都累计 已采购数量
            DbCommand dbCommandSql = DB.GetSqlStringCommand(" SELECT ID,ItemCode  FROM dbo.PToolingCharge ");
            DataTable DT = DB.ExecuteDataSet(dbCommandSql).Tables[0];
            int id = 0;
            string itemcode = "";
            foreach (DataRow DR in DT.Rows)
            {
                id = Convert.ToInt32(DR["id"]);
                itemcode = Convert.ToString(DR["ItemCode"]);

                DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOPOR", id, itemcode,DataSource, UserID, Password, InitialCatalog);
                try
                {
                    DB.ExecuteNonQuery(dbCommand);
                }
                catch (Exception e)
                {
                    returnstr+= e.Message;
                }
            }
            return returnstr;
        }
        #endregion
        /// <summary>
        /// 每天更新应收帐款　逾期款,及更新未放款 的到 已放款
        /// </summary>
        /// <returns></returns>
        public static string UpdateFTradeReceivables()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            //1.更新终端状态
            try
            {
                //更新未放款 的到 已放款
                //UPDATE  dbo.FTradeReceivables  SET ZDXiaoShouState='融资租赁(已放款)' WHERE ZDXiaoShouState='融资租赁(未放款)' AND(SELECT COUNT(SerialNumber) FROM dbo.FXFinanceLease WHERE LeaseStatus=4 AND SerialNumber=FTradeReceivables.JiHao AND AliasName=FTradeReceivables.JingXiaoShang)>0
                DbCommand dbCommandFX = DB.GetSqlStringCommand("UPDATE  dbo.FTradeReceivables  SET ZDXiaoShouState='融资租赁(已放款)' WHERE ZDXiaoShouState='融资租赁(未放款)' AND(SELECT COUNT(SerialNumber) FROM dbo.FXFinanceLease WHERE LeaseStatus=4 AND SerialNumber=FTradeReceivables.JiHao )>0");
                DB.ExecuteNonQuery(dbCommandFX);
            }
            catch
            { 

            }

            //2.更新应收主数据
            DbCommand dbCommand = DB.GetSqlStringCommand("SELECT DISTINCT FTradeID AS FTradeID FROM dbo.FTradeReceivables,dbo.FTradeAgeDetail WHERE dbo.FTradeAgeDetail.FTradeSalesId=dbo.FTradeReceivables.FTradeSalesId AND [State]='正常'");
            try
            {
                DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];
                //
                foreach (DataRow dr in dt.Rows)
                {
                    try
                    {
                        // int FTradeID=Convert.ToInt32(FTradeID
                        DbCommand dbCommandExec = DB.GetStoredProcCommand("FTradeReceivables_ExceCalculate", Convert.ToInt32(dr["FTradeID"]));
                        DB.ExecuteNonQuery(dbCommandExec);
                    }
                    catch { }
                }

                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
            finally
            { 
                
            }

            //3.更新返回利息状态

            DbCommand dbCommand3 = DB.GetSqlStringCommand("SELECT FTradeID FROM dbo.FTradeReceivables WHERE FanHuanLiXiState<>'正常(已结算)' and FanHuanLiXi>0 AND JiQiState='正常'");
            try
            {
                DataTable dt = DB.ExecuteDataSet(dbCommand3).Tables[0];
                //
                foreach (DataRow dr in dt.Rows)
                {
                    try
                    {
                        // int FTradeID=Convert.ToInt32(FTradeID
                        DbCommand dbCommandExec = DB.GetStoredProcCommand("FTradeReceivables_ExceFanHuanLiXiCalculate", Convert.ToInt32(dr["FTradeID"]));
                        DB.ExecuteNonQuery(dbCommandExec);
                    }
                    catch { }
                }

                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
            finally
            {

            }
        }
        

       
        /// <summary>
        /// 同步转库机器最后存放地
        /// </summary>
        /// <returns></returns>
        public static string SyncOSRI()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("SAP_SyncOSRI", DataSource, UserID, Password, InitialCatalog);
            try
            {
                DB.ExecuteNonQuery(dbCommand);
                return "";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }


        public static string ProcessUClaimDemagePjSend()
        {
            string restr = "";

            DataTableCollection dts = UClaimDemageController.GetSPSendList();
            DataTable dtmain = dts[0];
            DataTable dtdetail = dts[1];

            DataTable sboagentlist = SBOController.GetAgentList();
            string SerialNumber = "";//	机 号
            string Dscription = "";//	物料名称
            decimal Quantity = 0m;//	数量
            string U_ExpressMode = "";//	运输方式
            string U_InvoiceNum = "";//	发货单号
            string AgentName = "";//	代理商名称
            string TaxDate = "";//发货日期
            int ID = 0;

            string PJAcceptName = "";//	配件接收人
            string PJAcceptMobile = "";//	配件接收人手机
            string smstemplete = "{0},我司已于{1}通过{2}{4}发送以下索赔件:{3}.请注意查收.[石川岛中骏]";
            string smscontent = "";
            foreach (DataRow drmain in dtmain.Rows)
            {
                try
                {

                    U_ExpressMode = drmain["U_ExpressMode"].ToString();
                    AgentName = drmain["AgentName"].ToString();
                    TaxDate = Convert.ToDateTime(drmain["TaxDate"]).ToString("yyyy-MM-dd");
                    U_InvoiceNum = drmain["U_InvoiceNum"].ToString();
                    if (AgentName == "" || (AgentName is DBNull))
                        continue;
                    DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AgentName.Trim()));
                    
                    PJAcceptName = drs[0]["PJAcceptName"].ToString();
                    PJAcceptMobile = drs[0]["PJAcceptMobile"].ToString();               
                 
                    if (!string.IsNullOrEmpty(PJAcceptMobile))
                    {
                        if (!string.IsNullOrEmpty(PJAcceptName))
                        {
                            //查找相应的明细
                            DataRow[] senddrs = dtdetail.Select(string.Format(" AgentName ='{0}' and TaxDate='{1}' and U_ExpressMode='{2}'", AgentName.Trim(), TaxDate, U_ExpressMode));
                            smscontent = "";
                            int rowindex=1;
                            foreach (DataRow dr in senddrs)
                            { 
                                SerialNumber = dr["SerialNumber"].ToString();
                                Dscription = dr["Dscription"].ToString();
                                Quantity = Convert.ToDecimal( dr["Quantity"]);                               
                                ID =Convert.ToInt32( dr["ID"].ToString());
                                smscontent += string.Format("【{0}】{1}({2}件);对应机号:{3}.", rowindex++, Dscription, Quantity, SerialNumber);
                                //UClaimDemageController.UpdateSendSMS(ID, true, "");
                            }
                            if (rowindex > 1)
                            {
                                SMSProccessController.Add("索赔件发货", PJAcceptMobile, string.Format(smstemplete, AgentName, TaxDate, U_ExpressMode, smscontent, string.IsNullOrEmpty(U_InvoiceNum) ? "" : "(运单号:" + U_InvoiceNum + ")"));
                            }
                                ////添加短信.
                            //SMSProccessController.Add("索赔件发货", PJAcceptMobile, string.Format(smstemplete, PJAcceptName, TaxDate, U_ExpressMode, string.IsNullOrEmpty(U_InvoiceNum) ? "" : "(单号:" + U_InvoiceNum + ")", Dscription, Quantity, SerialNumber));
                            ////
                            //UClaimDemageController.UpdateSendSMS(ID, true, "");
                        }
                        else
                        {
                            UClaimDemageController.UpdateSendSMS(ID, false, "找不到相应的接收人");
                        }
                    }
                    else
                    {
                        UClaimDemageController.UpdateSendSMS(ID, false, "找不到相应接收的手机号");
                    }

                    restr += "";
                }
                catch (Exception exc)
                {
                    UClaimDemageController.UpdateSendSMS(ID, false, AgentName + "无法找到相应代理商信息");
                    restr += exc.Message +"["+ AgentName + "]无法找到相应代理商信息";
                }
            }

            return restr;
        }

        public static string ProcessUClaimDemagePjSend_old()
        {
            string restr = "";

            int TotalCount = 0;
            string sqlwhere = " SendMsg=0 and U_OutTypeText='索赔出库' and SendRemark is null ";
            DataTable dts = UClaimDemageController.GetPagination(sqlwhere, 1, 99999, " ID ASC ", ref TotalCount)[0];
            DataTable sboagentlist = SBOController.GetAgentList();
            string SerialNumber="";//	机 号
            string Dscription="";//	物料名称
            decimal Quantity=0m;//	数量
            string U_ExpressMode="";//	运输方式
            string U_InvoiceNum="";//	发货单号
            string AgentName="";//	代理商名称
            string TaxDate = "";//发货日期
            int ID = 0;

            string PJAcceptName="";//	配件接收人
            string PJAcceptMobile="";//	配件接收人手机
            /*
                经销商,我司已于 时间 通过 物流 (单号) 发送 物料 ,数量为 2,对应机号为 0.请注意查收.[石川岛中骏]
             */
            string smstemplete = "{0},我司已于{1}通过{2}{3}发送索赔件 {4}(机号{6}),数量 {5},请注意查收.[石川岛中骏]";
            foreach (DataRow dr in dts.Rows)
            {
                SerialNumber = dr["SerialNumber"].ToString();
                Dscription = dr["Dscription"].ToString();
                Quantity = Convert.ToDecimal( dr["Quantity"]);
                U_ExpressMode = dr["U_ExpressMode"].ToString();
                U_InvoiceNum = dr["U_InvoiceNum"].ToString();
                AgentName = dr["AgentName"].ToString();
                TaxDate = Convert.ToDateTime( dr["TaxDate"]).ToString("yyyy-MM-dd");
                ID =Convert.ToInt32( dr["ID"].ToString());
                try
                {
                    DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AgentName.Trim()));
                    PJAcceptName = drs[0]["PJAcceptName"].ToString();
                    PJAcceptMobile = drs[0]["PJAcceptMobile"].ToString();
                    if (!string.IsNullOrEmpty(PJAcceptMobile))
                    {
                        if (!string.IsNullOrEmpty(PJAcceptName))
                        {
                            //添加短信.
                            SMSProccessController.Add("索赔件发货", PJAcceptMobile, string.Format(smstemplete, PJAcceptName, TaxDate, U_ExpressMode, string.IsNullOrEmpty(U_InvoiceNum) ? "" : "(单号:" + U_InvoiceNum + ")", Dscription, Quantity, SerialNumber));
                            //
                            UClaimDemageController.UpdateSendSMS(ID, true, "");
                        }
                        else
                        {
                            UClaimDemageController.UpdateSendSMS(ID, false, "找不到相应的接收人");
                        }
                    }
                    else
                    {
                        UClaimDemageController.UpdateSendSMS(ID, false, "找不到相应接收的手机号");
                    }
                    restr+= "";

                }
                catch(Exception exc)
                {
                    UClaimDemageController.UpdateSendSMS(ID, false, AgentName+"无法找到相应代理商信息");
                    restr+=exc.Message;
                }
               
            }
            return restr;
        }
    }
}
